with center_collection as (
 select first_code, center_code, max(center_name) as center_name
         from (
                  select a.code                                               as first_code,
                         if(a.center_name like '%集散点', b.code, a.center_code) as center_code,
                         if(a.center_name like '%集散点', b.name, a.center_name) as center_name
                  from jms_dim.dim_lmdm_sys_first_code a
                           left join jms_dim.dim_lmdm_sys_network_distributi b
                                     on a.center_id = b.network_id
                                         and b.distribution_type = 1
                  where a.is_enable = 1
                    and a.is_delete = 1
              ) a
         group by first_code, center_code
),
signed_waybill_trace as (
select
a.waybill_no
,a.waybill_first_scantime
,a.waybill_first_scantype
,a.waybill_end_scantime
,a.waybill_end_scantype
,a.waybill_all_operation_times
,a.center_all_operation_times
,a.nodal_all_operation_times
,a.network_all_count
,a.center_all_count
,a.nodal_all_count
,a.actual_taking_scantime
,a.actual_taking_scansitecode
,a.actual_taking_weight
,a.actual_taking_scantype
,a.taking_later_scantime
,a.taking_early_scantime
,a.taking_early_scansitecode
,a.taking_early_weight
,a.warehousing_later_scantime
,a.warehousing_early_scantime
,a.warehousing_early_scansitecode
,a.warehousing_early_weight
,a.bagging_early_scantime
,a.bagging_early_scansitecode
,a.bagging_early_package_code
,a.first_network_network_code
,a.first_network_loading_later_time
,a.first_network_loading_later_weight
,a.first_network_send_later_time
,a.first_network_send_later_weight
,a.first_network_loading_send_shipment_no
,a.first_network_loading_send_planned_departure_time
,a.first_network_loading_send_planned_arrival_time
,a.first_network_loading_send_actual_departure_time
,a.first_network_loading_send_actual_arrival_time
,a.first_network_loading_send_next_network_code
,a.first_network_loading_send_next_network_type
,a.first_nodal_network_code
,a.first_nodal_unloading_early_time
,a.first_nodal_unloading_early_weight
,a.first_nodal_arrival_early_time
,a.first_nodal_arrival_early_weight
,a.first_nodal_unloading_arrival_shipment_no
,a.first_nodal_unloading_arrival_planned_departure_time
,a.first_nodal_unloading_arrival_planned_arrival_time
,a.first_nodal_unloading_arrival_actual_departure_time
,a.first_nodal_unloading_arrival_actual_arrival_time
,a.first_nodal_loading_later_time
,a.first_nodal_loading_later_weight
,a.first_nodal_send_later_time
,a.first_nodal_send_later_weight
,a.first_nodal_loading_send_shipment_no
,a.first_nodal_loading_send_planned_departure_time
,a.first_nodal_loading_send_planned_arrival_time
,a.first_nodal_loading_send_actual_departure_time
,a.first_nodal_loading_send_actual_arrival_time
,a.first_nodal_loading_send_next_network_code
,a.first_nodal_loading_send_next_network_type
,a.first_center_network_code
,a.first_center_unloading_early_time
,a.first_center_unloading_early_weight
,a.first_center_arrival_early_time
,a.first_center_arrival_early_weight
,a.first_center_unloading_arrival_shipment_no
,a.first_center_unloading_arrival_planned_departure_time
,a.first_center_unloading_arrival_planned_arrival_time
,a.first_center_unloading_arrival_actual_departure_time
,a.first_center_unloading_arrival_actual_arrival_time
,a.first_center_loading_later_time
,a.first_center_loading_later_weight
,a.first_center_send_later_time
,a.first_center_send_later_weight
,a.first_center_loading_send_shipment_no
,a.first_center_loading_send_planned_departure_time
,a.first_center_loading_send_planned_arrival_time
,a.first_center_loading_send_actual_departure_time
,a.first_center_loading_send_actual_arrival_time
,a.first_center_loading_send_next_network_code
,a.first_center_loading_send_next_network_type
,a.second_center_network_code
,a.second_center_unloading_early_time
,a.second_center_unloading_early_weight
,a.second_center_arrival_early_time
,a.second_center_arrival_early_weight
,a.second_center_unloading_arrival_shipment_no
,a.second_center_unloading_arrival_planned_departure_time
,a.second_center_unloading_arrival_planned_arrival_time
,a.second_center_unloading_arrival_actual_departure_time
,a.second_center_unloading_arrival_actual_arrival_time
,a.second_center_loading_later_time
,a.second_center_loading_later_weight
,a.second_center_send_later_time
,a.second_center_send_later_weight
,a.second_center_loading_send_shipment_no
,a.second_center_loading_send_planned_departure_time
,a.second_center_loading_send_planned_arrival_time
,a.second_center_loading_send_actual_departure_time
,a.second_center_loading_send_actual_arrival_time
,a.second_center_loading_send_next_network_code
,a.second_center_loading_send_next_network_type
,a.third_center_network_code
,a.third_center_unloading_early_time
,a.third_center_unloading_early_weight
,a.thirdcenter_arrival_early_time
,a.third_center_arrival_early_weight
,a.third_center_unloading_arrival_shipment_no
,a.third_center_unloading_arrival_planned_departure_time
,a.third_center_unloading_arrival_planned_arrival_time
,a.third_center_unloading_arrival_actual_departure_time
,a.third_center_unloading_arrival_actual_arrival_time
,a.third_center_loading_later_time
,a.third_center_loading_later_weight
,a.third_center_send_later_time
,a.third_center_send_later_weight
,a.third_center_loading_send_shipment_no
,a.third_center_loading_send_planned_departure_time
,a.third_center_loading_send_planned_arrival_time
,a.third_center_loading_send_actual_departure_time
,a.third_center_loading_send_actual_arrival_time
,a.third_center_loading_send_next_network_code
,a.third_center_loading_send_next_network_type
,a.fourth_center_network_code
,a.fourth_center_unloading_early_time
,a.fourth_center_unloading_early_weight
,a.fourth_center_arrival_early_time
,a.fourth_center_arrival_early_weight
,a.fourth_center_unloading_arrival_shipment_no
,a.fourth_center_unloading_arrival_planned_departure_time
,a.fourth_center_unloading_arrival_planned_arrival_time
,a.fourth_center_unloading_arrival_actual_departure_time
,a.fourth_center_unloading_arrival_actual_arrival_time
,a.fourth_center_loading_later_time
,a.fourth_center_loading_later_weight
,a.fourth_center_send_later_time
,a.fourth_center_send_later_weight
,a.fourth_center_loading_send_shipment_no
,a.fourth_center_loading_send_planned_departure_time
,a.fourth_center_loading_send_planned_arrival_time
,a.fourth_center_loading_send_actual_departure_time
,a.fourth_center_loading_send_actual_arrival_time
,a.fourth_center_loading_send_next_network_code
,a.fourth_center_loading_send_next_network_type
,a.end_center_network_code
,a.end_center_unloading_early_time
,a.end_center_unloading_early_weight
,a.end_center_arrival_early_time
,a.end_center_arrival_early_weight
,a.end_center_unloading_arrival_shipment_no
,a.end_center_unloading_arrival_planned_departure_time
,a.end_center_unloading_arrival_planned_arrival_time
,a.end_center_unloading_arrival_actual_departure_time
,a.end_center_unloading_arrival_actual_arrival_time
,a.end_center_loading_later_time
,a.end_center_loading_later_weight
,a.end_center_send_later_time
,a.end_center_send_later_weight
,a.end_center_loading_send_shipment_no
,a.end_center_loading_send_planned_departure_time
,a.end_center_loading_send_planned_arrival_time
,a.end_center_loading_send_actual_departure_time
,a.end_center_loading_send_actual_arrival_time
,a.end_center_loading_send_next_network_code
,a.end_center_loading_send_next_network_type
,a.end_nodal_network_code
,a.end_nodal_unloading_early_time
,a.end_nodal_unloading_early_weight
,a.end_nodal_arrival_early_time
,a.end_nodal_arrival_early_weight
,a.end_nodal_unloading_arrival_shipment_no
,a.end_nodal_unloading_arrival_planned_departure_time
,a.end_nodal_unloading_arrival_planned_arrival_time
,a.end_nodal_unloading_arrival_actual_departure_time
,a.end_nodal_unloading_arrival_actual_arrival_time
,a.end_nodal_loading_later_time
,a.end_nodal_loading_later_weight
,a.end_nodal_send_later_time
,a.end_nodal_send_later_weight
,a.end_nodal_loading_send_shipment_no
,a.end_nodal_loading_send_planned_departure_time
,a.end_nodal_loading_send_planned_arrival_time
,a.end_nodal_loading_send_actual_departure_time
,a.end_nodal_loading_send_actual_arrival_time
,a.end_nodal_loading_send_next_network_code
,a.end_nodal_loading_send_next_network_type
,a.end_network_network_code
,a.end_network_unloading_early_time
,a.end_network_arrival_early_time
,a.deliver_early_scantime
,a.deliver_later_scantime
,a.deliver_later_scansitecode
,a.deliver_later_weight
,a.deliver_later_user
,a.sign_later_scantime
,a.sign_early_scantime
,a.sign_early_scansitecode
,a.sign_early_weight
,a.aging_sign_scantime
,a.aging_sign_scansitecode
,a.aging_sign_weight
,a.aging_sign_scantype
,a.actual_sign_scantime
,a.actual_sign_scansitecode
,a.actual_sign_weight
,a.actual_sign_scantype
,a.waybill_status_code
,a.goods_type_id
,a.goods_type_code
,a.goods_type_name
,a.goods_name
,a.dispatch_network_code
,a.first_code
,a.second_code
,a.third_code
,a.is_sign
,a.sign_time
,a.is_abnormal
,a.waybill_source_code
,a.waybill_source_name
,a.is_void
,a.is_refund
,a.pick_network_code
,a.pick_network_name
,a.input_time
,a.input_network_code
,a.settlement_weight
,a.order_source_code
,a.is_delete
,a.package_collect_weight
,a.package_receipt_weight
,a.waybill_weight
,a.center_name
,a.center_code
,a.ordersource_code
,a.ordersource_name
,a.send_earlist_scantime
,a.customer_code
,a.customer_name
,a.first_network_loading_send_oper_next_network_code
,a.first_network_loading_send_oper_next_network_name
,a.first_nodal_loading_send_oper_next_network_code
,a.first_nodal_loading_send_oper_next_network_name
,a.first_nodal_actual_pre_network_code
,a.first_nodal_actual_pre_network_type
,a.first_center_loading_send_oper_next_network_code
,a.first_center_loading_send_oper_next_network_name
,a.first_center_actual_pre_network_code
,a.first_center_actual_pre_network_type
,a.end_center_loading_send_oper_next_network_code
,a.end_center_loading_send_oper_next_network_name
,a.end_center_actual_pre_network_code
,a.end_center_actual_pre_network_type
,a.end_nodal_loading_send_oper_next_network_code
,a.end_nodal_loading_send_oper_next_network_name
,a.end_nodal_actual_pre_network_code
,a.end_nodal_actual_pre_network_type
,a.end_network_actual_pre_network_code
,a.end_network_actual_pre_network_type
,date_format(a.end_center_send_later_time,'yyyy-MM-dd')
 from jms_dwd.dwd_s03_whole_operations_waybill_trace_dt a where dt > date_add('{{ execution_date | cst_ds }}', -30) and is_sign <> 0
),
unsigned_waybill_trace as (
select * from jms_dwd.dwd_s03_whole_operations_waybill_trace_dt where dt > date_add('{{ execution_date | cst_ds }}', -30) and is_sign = 0
),
unsigned_end_center as (
select
a.waybill_no
,a.waybill_first_scantime
,a.waybill_first_scantype
,a.waybill_end_scantime
,a.waybill_end_scantype
,a.waybill_all_operation_times
,a.center_all_operation_times
,a.nodal_all_operation_times
,a.network_all_count
,a.center_all_count
,a.nodal_all_count
,a.actual_taking_scantime
,a.actual_taking_scansitecode
,a.actual_taking_weight
,a.actual_taking_scantype
,a.taking_later_scantime
,a.taking_early_scantime
,a.taking_early_scansitecode
,a.taking_early_weight
,a.warehousing_later_scantime
,a.warehousing_early_scantime
,a.warehousing_early_scansitecode
,a.warehousing_early_weight
,a.bagging_early_scantime
,a.bagging_early_scansitecode
,a.bagging_early_package_code
,a.first_network_network_code
,a.first_network_loading_later_time
,a.first_network_loading_later_weight
,a.first_network_send_later_time
,a.first_network_send_later_weight
,a.first_network_loading_send_shipment_no
,a.first_network_loading_send_planned_departure_time
,a.first_network_loading_send_planned_arrival_time
,a.first_network_loading_send_actual_departure_time
,a.first_network_loading_send_actual_arrival_time
,a.first_network_loading_send_next_network_code
,a.first_network_loading_send_next_network_type
,a.first_nodal_network_code
,a.first_nodal_unloading_early_time
,a.first_nodal_unloading_early_weight
,a.first_nodal_arrival_early_time
,a.first_nodal_arrival_early_weight
,a.first_nodal_unloading_arrival_shipment_no
,a.first_nodal_unloading_arrival_planned_departure_time
,a.first_nodal_unloading_arrival_planned_arrival_time
,a.first_nodal_unloading_arrival_actual_departure_time
,a.first_nodal_unloading_arrival_actual_arrival_time
,a.first_nodal_loading_later_time
,a.first_nodal_loading_later_weight
,a.first_nodal_send_later_time
,a.first_nodal_send_later_weight
,a.first_nodal_loading_send_shipment_no
,a.first_nodal_loading_send_planned_departure_time
,a.first_nodal_loading_send_planned_arrival_time
,a.first_nodal_loading_send_actual_departure_time
,a.first_nodal_loading_send_actual_arrival_time
,a.first_nodal_loading_send_next_network_code
,a.first_nodal_loading_send_next_network_type
,a.first_center_network_code
,a.first_center_unloading_early_time
,a.first_center_unloading_early_weight
,a.first_center_arrival_early_time
,a.first_center_arrival_early_weight
,a.first_center_unloading_arrival_shipment_no
,a.first_center_unloading_arrival_planned_departure_time
,a.first_center_unloading_arrival_planned_arrival_time
,a.first_center_unloading_arrival_actual_departure_time
,a.first_center_unloading_arrival_actual_arrival_time
,a.first_center_loading_later_time
,a.first_center_loading_later_weight
,a.first_center_send_later_time
,a.first_center_send_later_weight
,a.first_center_loading_send_shipment_no
,a.first_center_loading_send_planned_departure_time
,a.first_center_loading_send_planned_arrival_time
,a.first_center_loading_send_actual_departure_time
,a.first_center_loading_send_actual_arrival_time
,a.first_center_loading_send_next_network_code
,a.first_center_loading_send_next_network_type
,a.second_center_network_code
,a.second_center_unloading_early_time
,a.second_center_unloading_early_weight
,a.second_center_arrival_early_time
,a.second_center_arrival_early_weight
,a.second_center_unloading_arrival_shipment_no
,a.second_center_unloading_arrival_planned_departure_time
,a.second_center_unloading_arrival_planned_arrival_time
,a.second_center_unloading_arrival_actual_departure_time
,a.second_center_unloading_arrival_actual_arrival_time
,a.second_center_loading_later_time
,a.second_center_loading_later_weight
,a.second_center_send_later_time
,a.second_center_send_later_weight
,a.second_center_loading_send_shipment_no
,a.second_center_loading_send_planned_departure_time
,a.second_center_loading_send_planned_arrival_time
,a.second_center_loading_send_actual_departure_time
,a.second_center_loading_send_actual_arrival_time
,a.second_center_loading_send_next_network_code
,a.second_center_loading_send_next_network_type
,a.third_center_network_code
,a.third_center_unloading_early_time
,a.third_center_unloading_early_weight
,a.thirdcenter_arrival_early_time
,a.third_center_arrival_early_weight
,a.third_center_unloading_arrival_shipment_no
,a.third_center_unloading_arrival_planned_departure_time
,a.third_center_unloading_arrival_planned_arrival_time
,a.third_center_unloading_arrival_actual_departure_time
,a.third_center_unloading_arrival_actual_arrival_time
,a.third_center_loading_later_time
,a.third_center_loading_later_weight
,a.third_center_send_later_time
,a.third_center_send_later_weight
,a.third_center_loading_send_shipment_no
,a.third_center_loading_send_planned_departure_time
,a.third_center_loading_send_planned_arrival_time
,a.third_center_loading_send_actual_departure_time
,a.third_center_loading_send_actual_arrival_time
,a.third_center_loading_send_next_network_code
,a.third_center_loading_send_next_network_type
,a.fourth_center_network_code
,a.fourth_center_unloading_early_time
,a.fourth_center_unloading_early_weight
,a.fourth_center_arrival_early_time
,a.fourth_center_arrival_early_weight
,a.fourth_center_unloading_arrival_shipment_no
,a.fourth_center_unloading_arrival_planned_departure_time
,a.fourth_center_unloading_arrival_planned_arrival_time
,a.fourth_center_unloading_arrival_actual_departure_time
,a.fourth_center_unloading_arrival_actual_arrival_time
,a.fourth_center_loading_later_time
,a.fourth_center_loading_later_weight
,a.fourth_center_send_later_time
,a.fourth_center_send_later_weight
,a.fourth_center_loading_send_shipment_no
,a.fourth_center_loading_send_planned_departure_time
,a.fourth_center_loading_send_planned_arrival_time
,a.fourth_center_loading_send_actual_departure_time
,a.fourth_center_loading_send_actual_arrival_time
,a.fourth_center_loading_send_next_network_code
,a.fourth_center_loading_send_next_network_type
,a.end_center_network_code
,a.end_center_unloading_early_time
,a.end_center_unloading_early_weight
,a.end_center_arrival_early_time
,a.end_center_arrival_early_weight
,a.end_center_unloading_arrival_shipment_no
,a.end_center_unloading_arrival_planned_departure_time
,a.end_center_unloading_arrival_planned_arrival_time
,a.end_center_unloading_arrival_actual_departure_time
,a.end_center_unloading_arrival_actual_arrival_time
,a.end_center_loading_later_time
,a.end_center_loading_later_weight
,a.end_center_send_later_time
,a.end_center_send_later_weight
,a.end_center_loading_send_shipment_no
,a.end_center_loading_send_planned_departure_time
,a.end_center_loading_send_planned_arrival_time
,a.end_center_loading_send_actual_departure_time
,a.end_center_loading_send_actual_arrival_time
,a.end_center_loading_send_next_network_code
,a.end_center_loading_send_next_network_type
,a.end_nodal_network_code
,a.end_nodal_unloading_early_time
,a.end_nodal_unloading_early_weight
,a.end_nodal_arrival_early_time
,a.end_nodal_arrival_early_weight
,a.end_nodal_unloading_arrival_shipment_no
,a.end_nodal_unloading_arrival_planned_departure_time
,a.end_nodal_unloading_arrival_planned_arrival_time
,a.end_nodal_unloading_arrival_actual_departure_time
,a.end_nodal_unloading_arrival_actual_arrival_time
,a.end_nodal_loading_later_time
,a.end_nodal_loading_later_weight
,a.end_nodal_send_later_time
,a.end_nodal_send_later_weight
,a.end_nodal_loading_send_shipment_no
,a.end_nodal_loading_send_planned_departure_time
,a.end_nodal_loading_send_planned_arrival_time
,a.end_nodal_loading_send_actual_departure_time
,a.end_nodal_loading_send_actual_arrival_time
,a.end_nodal_loading_send_next_network_code
,a.end_nodal_loading_send_next_network_type
,a.end_network_network_code
,a.end_network_unloading_early_time
,a.end_network_arrival_early_time
,a.deliver_early_scantime
,a.deliver_later_scantime
,a.deliver_later_scansitecode
,a.deliver_later_weight
,a.deliver_later_user
,a.sign_later_scantime
,a.sign_early_scantime
,a.sign_early_scansitecode
,a.sign_early_weight
,a.aging_sign_scantime
,a.aging_sign_scansitecode
,a.aging_sign_weight
,a.aging_sign_scantype
,a.actual_sign_scantime
,a.actual_sign_scansitecode
,a.actual_sign_weight
,a.actual_sign_scantype
,a.waybill_status_code
,a.goods_type_id
,a.goods_type_code
,a.goods_type_name
,a.goods_name
,a.dispatch_network_code
,a.first_code
,a.second_code
,a.third_code
,a.is_sign
,a.sign_time
,a.is_abnormal
,a.waybill_source_code
,a.waybill_source_name
,a.is_void
,a.is_refund
,a.pick_network_code
,a.pick_network_name
,a.input_time
,a.input_network_code
,a.settlement_weight
,a.order_source_code
,a.is_delete
,a.package_collect_weight
,a.package_receipt_weight
,a.waybill_weight
,a.center_name
,a.center_code
,a.ordersource_code
,a.ordersource_name
,a.send_earlist_scantime
,a.customer_code
,a.customer_name
,a.first_network_loading_send_oper_next_network_code
,a.first_network_loading_send_oper_next_network_name
,a.first_nodal_loading_send_oper_next_network_code
,a.first_nodal_loading_send_oper_next_network_name
,a.first_nodal_actual_pre_network_code
,a.first_nodal_actual_pre_network_type
,a.first_center_loading_send_oper_next_network_code
,a.first_center_loading_send_oper_next_network_name
,a.first_center_actual_pre_network_code
,a.first_center_actual_pre_network_type
,a.end_center_loading_send_oper_next_network_code
,a.end_center_loading_send_oper_next_network_name
,a.end_center_actual_pre_network_code
,a.end_center_actual_pre_network_type
,a.end_nodal_loading_send_oper_next_network_code
,a.end_nodal_loading_send_oper_next_network_name
,a.end_nodal_actual_pre_network_code
,a.end_nodal_actual_pre_network_type
,a.end_network_actual_pre_network_code
,a.end_network_actual_pre_network_type
,date_format(a.end_center_send_later_time,'yyyy-MM-dd')
from unsigned_waybill_trace a left join center_collection b on a.first_code = b.first_code where a.end_center_network_code = b.center_code )

insert overwrite table jms_dwd.dwd_s03_waybill_trace_endcenter_s_dt partition(dt)
select
a.* from unsigned_end_center  a
union all
select b.* from signed_waybill_trace b;
